package com.ztz.dao;

import hibernatebeans.entity.GeneralLedgerEntity;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;
import pers.lt.jdbc.JdbcTemplate;
import pers.lt.jdbc.Page;
import pers.lt.tools.StringFacs;

import javax.annotation.Resource;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.UUID;

@Component("RepetitionDataDao")
public class RepetitionDataDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    public Page repetitionDataList(Page page) {
        String sql = "select * from mtpts.general_ledger_repetition";
        return jdbcTemplate.pageList(sql, page);
    }

    //导入查询
    public Page querylist(Page page) {
        String sql = "select g.* from mtpts.general_ledger as g,mtpts.queryimport as q  where g.line_No=q.line_No \n" +
                "and g.line_No=q.line_No\n" +
                "and g.drawing_No=q.drawing_No\n" +
                "and g.weld_No=q.weld_No\n" +
                "and g.mk=q.mk\n" +
                "and g.material_type=q.material_type\n" +
                "and g.line_class=q.line_class\n" +
                "and g.pipe_size=q.pipe_size\n" +
                "and g.welder_No=q.welder_No\n" +
                "and g.welding_method=q.welding_method\n" +
                "and g.join_type=q.join_type\n" +
                "and g.cwp=q.cwp\n" +
                "and g.contractor=q.contractor\n" +
                "and g.cut=q.cut\n" +
                "and g.fab=q.fab   and  (g.reason = q.reason\n" +
                "or g.check1 = q.check1\n" +
                "or g.check2 = q.check2\n" +
                "or g.check3 = q.check3\n" +
                "or g.check4 = q.check4\n" +
                ")";

      /*  if(!StringFacs.isEmpty(generalLedgerEntity.getDrawingNo())&& !generalLedgerEntity.getDrawingNo().equals("undefined")){
            sql+="and drawing_No like '%"+generalLedgerEntity.getDrawingNo().trim()+"%' ";
        }
        if(!StringFacs.isEmpty(generalLedgerEntity.getWeldNo()) && !generalLedgerEntity.getWeldNo().equals("undefined")){
            sql+="and weld_No='"+generalLedgerEntity.getWeldNo().trim()+"'";
        }*/
        Page page1 = jdbcTemplate.pageList(sql, page);
        return page1;
    }

    public Page querys(Page page, GeneralLedgerEntity generalLedgerEntity){
        String sql="select * from  general_ledger where 1=1";
         if(!StringFacs.isEmpty(generalLedgerEntity.getLineNo())&& !generalLedgerEntity.getLineNo().equals("undefined")){
            sql+="  and line_No like '%"+generalLedgerEntity.getLineNo().trim()+"%' ";
        }
        if(!StringFacs.isEmpty(generalLedgerEntity.getWeldNo()) && !generalLedgerEntity.getWeldNo().equals("undefined")){
            sql+="  and weld_No='"+generalLedgerEntity.getWeldNo().trim()+"'";
        }
        Page page1 = jdbcTemplate.pageList(sql, page);
        return page1;
    }
    //插入主表数据
    public boolean inserytUser(StringBuffer str) {
        StringBuffer strs = new StringBuffer();
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");

        strs.append("insert into mtpts.general_ledger values(" + "'" + uuid + "'");

        for (int i = 0; i < str.length(); i++) {
            strs.append(str.toString().toCharArray()[i]);
        }
        strs.append("" + ")");
      
        boolean ret = false;
        try {

            try {
                jdbcTemplate.excuteSql(strs.toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return ret;
    }
    //插入重复表
    public boolean insertUserRepetition(StringBuffer str ) {
        StringBuffer strs = new StringBuffer();
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");

        strs.append("insert into mtpts.general_ledger_repetition values(" + "'" + uuid + "'");

        for (int i = 0; i < str.length(); i++) {
            strs.append(str.toString().toCharArray()[i]);
        }
        strs.append("" + ")");
        boolean ret = false;
        try {
            try {
              
                ret = jdbcTemplate.excuteSql(strs.toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return ret;
    }
    //修改
    public boolean updateUserRepetition(String id,String a ,String b,String[] value,String[] key) {
        StringBuffer strs = new StringBuffer();
        strs.append("update general_ledger set "+b+"='"+a+"'");
        for (int i =0; i <14; i++) {
            strs.append(",");
            strs.append(key[i]).append("=").append("'");
            strs.append(value[i]).append("'");

        }
        strs.append("    where id='"+id+"' ");
      
        try {
            jdbcTemplate.excuteSql(strs.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return true;
    }
   // 数据查询按钮导出
    public List<Map> exportExcel1(String line_No, String weldNo, String flag) {
        String ssql = "";
        //是从导入数据页面的导出
        if (flag == null) {
            String sql = "select * from mtpts.general_ledger where 1=1 ";
            ssql = sql;
        } else {
            if (flag.equals("1")) {//1表示是从查询页面的导出
                String sql = "select * from mtpts.general_ledger where 1=1 ";
                if (!StringFacs.isEmpty(line_No) && !line_No.equals("undefined")) {
                    sql += " and  line_No like '%" + line_No.trim() + "%' ";
                }
                if (!StringFacs.isEmpty(weldNo) && !weldNo.equals("undefined")) {
                    sql += "and weld_No='" + weldNo.trim() + "'";
                }
                ssql = sql;
            }
        }


        List list = jdbcTemplate.queryDatas(ssql);
        return list;
    }

    //  查询是否有重复
    public List<Map> sercaList() {
        String sql = "SELECT id,concat(t.line_No,t.weld_No,t.pipe_size,t.cut,t.fab,t.reason) as zf FROM mtpts.general_ledger as t where 1=1";
        List list = jdbcTemplate.queryDatas(sql);
        return list;
    }
    //  查询检测方式是否重复
    public List<Map> sercaListjcsf(String zh) {
        String sql = "SELECT t.id,t.check1,t.check2,t.check3,t.check4,concat(t.line_No,t.weld_No,t.pipe_size,t.cut,t.fab,t.reason)as zf FROM mtpts.general_ledger as t where  concat(t.line_No,t.weld_No,t.pipe_size,t.cut,t.fab,t.reason) ='"+zh+"'";
        List list = jdbcTemplate.queryDatas(sql);
        return list;
    }
    //  查询是否有需要修改
    public List<Map> sercaListup() {
        String sql = "SELECT concat(t.line_No,t.weld_No,t.pipe_size,t.cut,t.fab,t.reason,t.check1,t.check2,t.check3,t.check4) as zf FROM mtpts.general_ledger as t where 1=1";
        List list = jdbcTemplate.queryDatas(sql);
        return list;
    }

    //删除
    public void sjDelete(List list) throws SQLException {
        jdbcTemplate.deleteAll(list);
    }

    public void deleteAll() {
        try {
            jdbcTemplate.excuteSql("delete from mtpts.general_ledger_repetition");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * ----------------------------------------------------------------------------------------------------
     *
     * @return
     */
//导入查询保存临时数据
    public boolean insertUser(StringBuffer str) {

        boolean ret = false;
        StringBuffer strs = new StringBuffer();
        String uuid = UUID.randomUUID().toString().replaceAll("-", "");

        strs.append("insert into mtpts.queryimport values(" + "'" + uuid + "'" + "");
        for (int i = 0; i < str.length(); i++) {
            strs.append(str.toString().toCharArray()[i]);
        }
        //strs.append(""+"'"+repetition+"'");
        strs.append("" + ")");
        try {
            try {
                ret = jdbcTemplate.excuteSql(strs.toString());
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
                }
        return ret;
    }

    //根据导入数据导出
    public String exportExcellist() throws SQLException {
        //是从导入数据页面的导出
        String sql = "select querycolums from mtpts.queryimport";
        List list=jdbcTemplate.queryDatas(sql);
        String querycolums="";
        for (int i=0;i<list.size();i++){
            Map map=(Map) list.get(i);
            querycolums=map.get("querycolums").toString();
        }
        return querycolums;
    }

    public void del() {
        String del="delete from mtpts.queryimport";
        //先删除临时表
        try {
            jdbcTemplate.excuteSql(del);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //导出查询
    public List<Map> exportExcellist1() {
        String sql = "select g.* from mtpts.general_ledger as g,mtpts.queryimport as q  where g.line_No=q.line_No \n" +
                "and g.line_No=q.line_No\n" +
                "and g.drawing_No=q.drawing_No\n" +
                "and g.weld_No=q.weld_No\n" +
                "and g.mk=q.mk\n" +
                "and g.material_type=q.material_type\n" +
                "and g.line_class=q.line_class\n" +
                "and g.pipe_size=q.pipe_size\n" +
                "and g.welder_No=q.welder_No\n" +
                "and g.welding_method=q.welding_method\n" +
                "and g.join_type=q.join_type\n" +
                "and g.cwp=q.cwp\n" +
                "and g.contractor=q.contractor\n" +
                "and g.cut=q.cut\n" +
                "and g.fab=q.fab   and  (g.reason = q.reason\n" +
                "or g.check1 = q.check1\n" +
                "or g.check2 = q.check2\n" +
                "or g.check3 = q.check3\n" +
                "or g.check4 = q.check4\n" +
                ")";
        List list = jdbcTemplate.queryDatas(sql);
        return list;
    }
}
